﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados.Enum;
using System.Data;

/// <summary>
/// Summary description for callStatusDAO
/// </summary>
/// 
namespace br.com.SistemaChamados.DAO
{
    public class PlaceDAO
    {
        private static string SQL_UPDATE_PLACE = "UPDATE TBL_PLACE SET TBL_PLACEZIP = @placezip, TBL_PLACEADDRESS = @placeaddress, TBL_PLACECOMPLEMENT = @placecomplement, TBL_PLACENUMBER = @placenumber, TBL_CITYID = @cityid WHERE  TBL_PLACEID = @placeid";
        private static string SQL_DELETE_PLACE = " DELETE FROM TBL_PLACE WHERE TBL_PLACEID = @id";
        private static string SQL_INSERT_PLACE = "INSERT INTO TBL_PLACE (TBL_PLACEZIP, TBL_PLACEADDRESS, TBL_PLACECOMPLEMENT, TBL_PLACENUMBER, TBL_CITYID) VALUES (@placezip, @placeaddress, @placecomplement, @placenumber, @cityid)";
        private static string SQL_GET_PLACE = " SELECT * FROM TBL_PLACE WHERE TBL_PLACEID = @id";
        private static string SQL_GET_PLACES = " SELECT * FROM TBL_PLACE";

        public EnumGenericState UpdatePlace(Place place)
        {
            EnumGenericState resultPlace = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_PLACE, conn);
                command.Parameters.Add(Mapped.Parameter("@placeid", place.Id));
                command.Parameters.Add(Mapped.Parameter("@placezip", place.ZipCode));
                command.Parameters.Add(Mapped.Parameter("@placeaddress", place.Address));
                command.Parameters.Add(Mapped.Parameter("@placecomplement", place.Complement));
                command.Parameters.Add(Mapped.Parameter("@placenumber", place.Number));
                command.Parameters.Add(Mapped.Parameter("@cityid", place.City.Id));
                command.ExecuteNonQuery();
                resultPlace = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                resultPlace = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultPlace;
        }

        public EnumGenericState DeletePlace(Place place)
        {
            EnumGenericState resultPlace = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_PLACE, conn);
                command.Parameters.Add(Mapped.Parameter("@id", place.Id));
                command.ExecuteNonQuery();
                resultPlace = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                resultPlace = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultPlace;
        }

        public EnumGenericState InsertPlace(Place place)
        {
            EnumGenericState resultPlace = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_PLACE, conn);
                command.Parameters.Add(Mapped.Parameter("@placezip", place.ZipCode));
                command.Parameters.Add(Mapped.Parameter("@placeaddress", place.Address));
                command.Parameters.Add(Mapped.Parameter("@placecomplement", place.Complement));
                command.Parameters.Add(Mapped.Parameter("@placenumber", place.Number));
                command.Parameters.Add(Mapped.Parameter("@cityid", place.City.Id));
                command.ExecuteNonQuery();
                resultPlace = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                resultPlace = EnumGenericState.GENERIC_STATE_FAILED;
                place = null;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultPlace;
        }
        
        public Place GetPlace(Place place)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            Place resultPlace = null;
            City city = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_PLACE, conn);
                command.Parameters.Add(Mapped.Parameter("@id", place.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    resultPlace = new Place();
                    city = new City();
                    resultPlace.Id = Convert.ToInt32(reader["TBL_PLACEID"]);
                    resultPlace.ZipCode = Convert.ToInt32(reader["TBL_PLACEZIP"]);
                    resultPlace.Address = Convert.ToString(reader["TBL_PLACEADDRESS"]);
                    resultPlace.Complement = Convert.ToString(reader["TBL_PLACECOMPLEMENT"]);
                    resultPlace.Number = Convert.ToInt32(reader["TBL_PLACENUMBER"]);
                    city.Id = Convert.ToInt32(reader["TBL_CITYID"]);
                    resultPlace.City = city;
                }                
            }
            catch (Exception ex)
            {
                resultPlace = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return resultPlace;
        }

        public List<Place> GetPlaces()
        {
            List<Place> list = new List<Place>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            Place place = null;
            City city = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_PLACES, conn);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    place = new Place();
                    city = new City();
                    place.Id = Convert.ToInt32(reader["TBL_PLACEID"]);
                    place.ZipCode = Convert.ToInt32(reader["TBL_PLACEZIP"]);
                    place.Address = Convert.ToString(reader["TBL_PLACEADDRESS"]);
                    place.Complement = Convert.ToString(reader["TBL_PLACECOMPLEMENT"]);
                    place.Number = Convert.ToInt32(reader["TBL_PLACENUMBER"]);
                    city.Id = Convert.ToInt32(reader["TBL_CITYID"]);
                    place.City = city;
                    list.Add(place);
                }
                return list;
            }
            catch (Exception ex)
            {
                list = null;
                return list;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();                
            }
            
        }
        public PlaceDAO()
        {
            //
            // TODO: Add constructor logic here
            //
        }
    }
}